/*
 DNN Content Builder - sourceforge.net/projects/dnncb
 Copyright (c) 2004-2008
 by Emerson Lopes (emersonlopes@gmail.com)

 Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
 documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
 the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
 to permit persons to whom the Software is furnished to do so, subject to the following conditions:

 The above copyright notice and this permission notice shall be included in all copies or substantial portions 
 of the Software.

 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
 TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
 THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
 CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
 DEALINGS IN THE SOFTWARE.
*/

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Web.UI;
using DotNetNuke.Common.Utilities;
using DotNetNuke.Entities.Users;
using DotNetNuke.Security.Roles;

namespace DNNContentBuilder
{
	/// <summary>
	/// Summary description for DbContentController.
	/// </summary>
	public class DbContentController
	{
		#region Class Members
		private const string COMMA = ", ";
		private const string IGNORE_USER_ROLES = "<IGNORE_ROLES>";
		
		private readonly int formDefinitionId;
		private readonly int portalId;
		private Guid foreignKeyValue;
		private readonly Hashtable fieldCache = new Hashtable();
		private readonly Hashtable formlets;
		#endregion

		#region Constructors
		/// <summary>
		/// Initializes a new instance of the DbContentController class.
		/// </summary>
		/// <param name="formDefinitionId"></param>
		/// <param name="portalId"></param>
		/// <param name="formlets"></param>
		public DbContentController(int formDefinitionId, int portalId, Hashtable formlets)
		{
			this.formDefinitionId = formDefinitionId;
			this.portalId = portalId;
			this.formlets = formlets;
		}

		/// <summary>
		/// Initializes a new instance of the DbContentController class.
		/// </summary>
		/// <param name="formDefinitionId"></param>
		public DbContentController(int formDefinitionId) : this(formDefinitionId, Null.NullInteger, null)
		{
		}

		/// <summary>
		/// Initializes a new instance of the DbContentController class.
		/// </summary>
		/// <param name="formDefinitionId"></param>
		/// <param name="portalId"></param>
		public DbContentController(int formDefinitionId, int portalId) : this(formDefinitionId, portalId, null)
		{
		}
		#endregion

		#region Properties
		public Guid ForeignKeyValue
		{
			get
			{
				return foreignKeyValue;
			}
			set
			{
				foreignKeyValue = value;
			}
		}
		#endregion

		#region Constants
/*
		private const string resource = "~/DesktopModules/DNNContentBuilder/App_LocalResources/ResourceEngine";
*/
		private const string eventResource = "~/DesktopModules/DNNContentBuilder/App_LocalResources/MetaDataEvent";
		#endregion
		
		#region Public Methods
		public static void UnPublish(Guid pkValue, int userId)
		{
			using (ConnectionProvider cp = new ConnectionProvider())
			using (MetaDataController controller = new MetaDataController(cp))
			using (MetaDataEventController meController = new MetaDataEventController(cp))
			{
				cp.BeginTransaction();

				MetaDataInfo mdInfo = controller.GetMetaDataByPKValue(pkValue.ToString(), IGNORE_USER_ROLES);

				// Make sure we have valid working data
				ArgumentValidator.AssertNotNull(mdInfo, "METADATA_NOT_FOUND", pkValue);

				if (mdInfo.Status == MetaDataStatus.Published)
				{
					// Change status back to Working
					mdInfo.Status = MetaDataStatus.Working;
					mdInfo.PublishDate = Null.NullDate;
					mdInfo.ScheduleDate = Null.NullDate;
					controller.Update(mdInfo);
				}

				MetaDataEventInfo meInfo = new MetaDataEventInfo();
					
				meInfo.Description = Commons.GetLocalizedString("label_ContentUnpublished", eventResource);
				meInfo.Details     = mdInfo.Title;
				meInfo.MetaDataId  = mdInfo.MetaDataId;
				meInfo.EventDate   = DateTime.Now;
				meInfo.UserId      = userId;

				meInfo.EventType = TypeOfEvent.Unpublished;

				meController.Add(meInfo);

				cp.Commit();
			}
		}

		public void Delete(Guid pkValue, ConnectionStringInfo csInfo)
		{
			OleDbTransaction trans = null;

			using (ConnectionProvider cp = new ConnectionProvider())
			using (OleDbConnection conn = new OleDbConnection(csInfo.ConnectionString))
			{
				try
				{
					
					MetaDataController controller = new MetaDataController();
					MetaDataInfo mdInfo = controller.GetMetaDataByPKValue(pkValue.ToString(), IGNORE_USER_ROLES);

					ArgumentValidator.AssertNotNull(mdInfo, "METADATA_NOT_FOUND", pkValue);

					conn.Open();
					
					trans = conn.BeginTransaction();

					using (OleDbCommand command = GetDeleteCommand(trans, pkValue, mdInfo))
					{
						int affectedRows;
						
						try {
							affectedRows = command.ExecuteNonQuery();
						}
						catch (Exception )
						{
							//TODO: to implement some way to display error messages
							trans.Rollback();
							
							throw new ApplicationException("ERR_DELETION_CANCELED");
						}
						
						if (affectedRows == 0)
						{
							throw new ApplicationException("DELETE_STATEMENT_FAILED");
						}

						Debug.Assert(affectedRows > 0);
					}
					
					controller.Delete(mdInfo.MetaDataId);

					trans.Commit();
					cp.Commit();
				}
				catch (DataException)
				{
					if (trans != null)
					{
						trans.Rollback();
					}

					cp.Rollback();
				}
			}
		}

		/// <summary>
		/// Generate and execute the SQL statements necessary to UPDATE the 
		/// data store.
		/// </summary>
		/// <param name="info"></param>
		/// <param name="csInfo"></param>
		/// <param name="connectionInfo"></param>
		/// <param name="cmsInfo"></param>
		/// <param name="contentSecurity"></param>
		/// <param name="userId"></param>
		public int Update(MetaDataInfo info, ConnectionStringInfo csInfo, DataConnectionInfo connectionInfo, ContentManagerSettingsInfo cmsInfo, ArrayList contentSecurity, int userId)
		{
			int result = Null.NullInteger;
			bool newData = false;

			Guid pkValue = info != null ? new Guid(info.PKValue) : Guid.NewGuid();

		    using (MetaDataController controller = new MetaDataController())
			{
				newData = info == null || controller.GetMetaDataByPKValue(pkValue.ToString(), IGNORE_USER_ROLES) == null;
			}
			
			// TODO: OleDbConnection x SqlConnection = distributed transaction. Address this in v2.0
			using (OleDbConnection conn = new OleDbConnection(csInfo.ConnectionString))
			using (IDbCommand command = newData ? GetInsertCommand(conn, connectionInfo, pkValue) : GetUpdateCommand(conn, connectionInfo, pkValue))
			{
				conn.Open();

			    int affectedRows = 0;
			    try 
				{
					affectedRows = command.ExecuteNonQuery();
				}
				catch (Exception ) {
					throw new ApplicationException("OPERATION_ABORTED"); 
				}

				if (affectedRows == 0)
				{
					throw new ApplicationException("UPDATE_STATEMENT_FAILED");
				}
			}

			if (info != null)
			{
				result = UpdateMetaData(newData, info, cmsInfo, contentSecurity);
			}

			MetaDataEventController meController = new MetaDataEventController();
					
			MetaDataEventInfo meInfo = new MetaDataEventInfo();
					
			meInfo.Description = newData ? Commons.GetLocalizedString("label_ContentCreated", eventResource) : Commons.GetLocalizedString("label_ContentUpdated", eventResource);
		    if (info != null) meInfo.Details     = info.Title;
		    if (info != null) meInfo.MetaDataId  = info.MetaDataId;
		    if (info != null) meInfo.EventDate   = info.CreationDate;
		    meInfo.UserId      = userId;

			meInfo.EventType = newData ? TypeOfEvent.Created : TypeOfEvent.Updated;

			meController.Add(meInfo);
			
			return result;
		}

		private static void ClearSecurityInfo(ContentSecurityController csController, MetaDataInfo mdInfo)
		{
			ArrayList contentSecurity = csController.GetByMetaData(mdInfo.MetaDataId);

			foreach (ContentSecurityInfo csInfo in contentSecurity)
			{
				csController.Delete(csInfo.ContentSecurityId);
			}
		}

		private static void AddSecurityInfo(ContentSecurityController csController, MetaDataInfo mdInfo, ArrayList contentSecurity)
		{
			foreach (string role in contentSecurity)
			{
				if (role != "") {
					ContentSecurityInfo csInfo = new ContentSecurityInfo();

					csInfo.ContentAuthorizedViewer = role;
					csInfo.MetaDataId = mdInfo.MetaDataId;

					csController.Add(csInfo);
				}
			}
		}

		private int UpdateMetaData(bool newData, MetaDataInfo info, ContentManagerSettingsInfo cmsInfo, ArrayList contentSecurity)
		{
			int result = Null.NullInteger;

			// TODO: enable transaction semantics in DbContentController.UpdateMetaData
			using (MetaDataController controller = new MetaDataController())
			using (ContentSecurityController securityController = new ContentSecurityController())
			{
				ClearSecurityInfo(securityController, info);

				if (newData)
				{
  					if (cmsInfo.WorkflowId  == Null.NullInteger) 
  					{
  						info.Status = MetaDataStatus.Working;
  					}

					info.MetaDataId = controller.Add(info); 

					if (cmsInfo.WorkflowId != Null.NullInteger)
					{
						StartWorkflow(info.MetaDataId, cmsInfo);
					}

					result = info.MetaDataId;
				}
				else
				{
					controller.Update(info);
				}

				AddSecurityInfo(securityController, info, contentSecurity);
			}

			return result;
		}

		private void StartWorkflow(int metaDataId, ContentManagerSettingsInfo cmsInfo)
		{
			CreateMetaDataTasks(cmsInfo, metaDataId);
		}

		public void ReStartWorkflow(int metaDataId, ContentManagerSettingsInfo cmsInfo)
		{
			// remove existing tasks and owners
			MetaDataTaskController mt = new MetaDataTaskController();
			
			mt.DeleteAll(metaDataId);
			
			CreateMetaDataTasks(cmsInfo, metaDataId);
		}
		
		private void CreateMetaDataTasks(ContentManagerSettingsInfo cmsInfo, int metaDataId)
		{
			TasksController source = new TasksController();
			MetaDataTaskController target = new MetaDataTaskController();
	
			ArrayList tasks = source.GetTasksByWorkflow(cmsInfo.WorkflowId);

			bool notify = true;
	
			// TODO: add transaction support
			foreach (TasksInfo task in tasks)
			{
				MetaDataTaskInfo metaDataTask = new MetaDataTaskInfo();

				metaDataTask.TaskOrder	= task.TaskOrder;
				metaDataTask.StartDate	= Null.NullDate;
				metaDataTask.Status		= TaskStatus.NotAssigned;
				metaDataTask.Title		= task.Title;
				metaDataTask.MetaDataId = metaDataId;
				metaDataTask.FinishDate = Null.NullDate;

				int metaDataTaskId = target.Add(metaDataTask);

				ArrayList owners = CreateMetaDataTaskOwners(task.TaskId, metaDataTaskId);

				//Send notification for this first task only
				if (notify)
				{
					SendNotification(owners, metaDataTaskId, cmsInfo);
					notify = false;
				}
			}
		}

		public void SendNotification(ArrayList owners, int metaDataTaskId,ContentManagerSettingsInfo cmsInfo) {
			ArrayList users = new ArrayList();

			foreach (TaskOwnerInfo taskOwner in owners)
			{
				if (taskOwner.OwnerType == "R")
				{
					RoleInfo role = new RoleController().GetRole(taskOwner.OwnerId, portalId);
							
					users = new RoleController().GetUserRolesByRoleName(this.portalId, role.RoleName);
					
					UserController userController = new UserController();
					
					foreach (UserRoleInfo userRoleInfo in users)
					{
						UserInfo user = userController.GetUser(portalId, userRoleInfo.UserID);
						
						// TODO: profile and optimize tight loop as required
						TaskNotification taskNotification = new TaskNotification(cmsInfo.WorkflowId, metaDataTaskId, user.UserID, portalId);
						taskNotification.Send();
					}
				}
				else
				{
					// TODO: profile and optimize tight loop as required
					TaskNotification taskNotification = new TaskNotification(cmsInfo.WorkflowId, metaDataTaskId, taskOwner.OwnerId, portalId);
					taskNotification.Send();
				}
			}
		}

		
		private static ArrayList CreateMetaDataTaskOwners(int taskId, int metaDataTaskId)
		{
			TaskOwnerController source = new TaskOwnerController();
			MetaDataTaskOwnerController target = new MetaDataTaskOwnerController();

			ArrayList owners = source.TaskOwnerGetTaskOwners(taskId);

			foreach (TaskOwnerInfo taskOwner in owners)
			{
				MetaDataTaskOwnerInfo metaDataTaskOwner = new MetaDataTaskOwnerInfo();

				metaDataTaskOwner.OwnerId = taskOwner.OwnerId;
				metaDataTaskOwner.OwnerType = taskOwner.OwnerType;
				metaDataTaskOwner.MetaDataTaskId = metaDataTaskId;

				target.Add(metaDataTaskOwner);
			}

			return owners;
		}

		/// <summary>
		/// Returns data from a configured data store.
		/// </summary>
		/// <returns></returns>
		public DataSet Select(MetaDataInfo info)
		{
			// Retrieve Connection String
			DataConnectionController dcController = new DataConnectionController();
			DataConnectionInfo dcInfo = dcController.GetItem(info.DataConnectionID);

			ConnectionStringController csController = new ConnectionStringController();
			ConnectionStringInfo csInfo = csController.GetItem(dcInfo.ConnectionStringId);
			
			string connectionString = csInfo.ConnectionString;

			// Fill a DataSet 
			DataSet ds = new DataSet();
			string cmdText = String.Format("select {0} from {1} where {2}", this.GetFieldsClause(dcInfo.TableName), dcInfo.TableName, this.GetWhereClause(info, dcInfo.TableName));

			using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmdText, connectionString))
			{
				adapter.Fill(ds);
			}

			return ds;
		}

		/// <summary>
		/// Generates a WHERE clause with a primary key and primary key value.
		/// </summary>
		/// <param name="info"></param>
		/// <returns></returns>
		/// <param name="tableName"></param>
		private string GetWhereClause(MetaDataInfo info, string tableName)
		{
			string result = String.Empty;
			ArrayList fields = this.GetFields(tableName);

			foreach (FieldDefinitionInfo fdInfo in fields)
			{
				if (fdInfo.IsPrimaryKey)
				{
					result = String.Format("{0} = '{1}'", fdInfo.SourceField, info.PKValue.ToString());
					break;
				}
			}
			
			return result;
		}

		/// <summary>
		/// Gets a comma-separated list of fields for use in a SQL Select statement
		/// </summary>
		/// <returns></returns>
		private string GetFieldsClause(string tableName)
		{
			string result = String.Empty;
			ArrayList fields = this.GetFields(tableName);

			for (int i = 0; i < fields.Count; i++)
			{
				FieldDefinitionInfo fdInfo = (FieldDefinitionInfo)fields[i];
				
				result += String.Format("{0} as [{0}]", fdInfo.SourceField);
				result += i < fields.Count - 1 ? COMMA : String.Empty;
			}

			return result;
		}

		/// <summary>
		/// Gets a list of field definitions associated with a specified 
		/// form definition.
		/// </summary>
		/// <param name="formDefinitionId"></param>
		/// <returns></returns>
		public static ArrayList GetFields(int formDefinitionId)
		{
			return new FieldDefinitionController().GetByFormDefinition(formDefinitionId);
		}

		/// <summary>
		/// Grabs a cached <see cref="ArrayList"/> of field definitions.
		/// </summary>
		/// <param name="tableName"></param>
		/// <returns>The cached array list, or null if not found.</returns>
		public ArrayList GetFields(string tableName)
		{
			ArrayList result = this.fieldCache[this.formDefinitionId] as ArrayList;
			ArrayList fields = new ArrayList();

			if (result == null)
			{
				result = GetFields(this.formDefinitionId);
				fields = new ArrayList(result);

				foreach (FieldDefinitionInfo fdInfo in fields)
				{
					if (fdInfo.TableName != tableName)
					{
						result.Remove(fdInfo);
					}
				}

				fieldCache.Add(this.formDefinitionId, result);
			}

			return result;
		}

		/// <summary>
		/// Gets the value of the first field marked with the "title" attribute
		/// </summary>
		/// <returns></returns>
		public string GetTitleFieldValue(string tableName)
		{
			// TODO: Err.. probably should not be public.
			ArrayList fields = this.GetFields(tableName);
			string result = String.Empty;

			if (fields != null && fields.Count > 0)
			{
				foreach (FieldDefinitionInfo fdInfo in fields)
				{
					if (fdInfo.IsTitle)
					{
						result = (string)this.GetFieldInfo(fdInfo).Third;
						break;
					}
				}
			}

			return result;
		}
		#endregion

		#region Private Methods
		private static OleDbCommand GetDeleteCommand(OleDbTransaction transaction, Guid pkValue, MetaDataInfo info)
		{
			OleDbConnection conn = transaction.Connection;
			OleDbCommand command = conn.CreateCommand();

			command.Transaction = transaction;

			const string cmdText = "DELETE FROM {0} WHERE {1} = ?";

			DataConnectionController dcController = new DataConnectionController();
			DataConnectionInfo dcInfo = dcController.GetItem(info.DataConnectionID);

			command.CommandText = String.Format(cmdText, dcInfo.TableName, dcInfo.PrimaryKeyName);
			command.CommandType = CommandType.Text;

			command.Parameters.AddWithValue(dcInfo.PrimaryKeyName,  pkValue.ToString());

			return command;
		}

		/// <summary>
		/// Generates an OleDB command suitable to insert data into a custom user store.
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="connectionInfo"></param>
		/// <param name="pkValue"></param>
		/// <returns></returns>
		private IDbCommand GetInsertCommand(IDbConnection conn, DataConnectionInfo connectionInfo, Guid pkValue)
		{
			IDbCommand command = conn.CreateCommand();

			string into = String.Empty, values = String.Empty;
			const string insertCommandText = "INSERT INTO {0} ({1}) VALUES ({2})";

			ArrayList fields = this.GetFields(connectionInfo.TableName);

			for (int i = 0; i < fields.Count; i++)
			{
				FieldDefinitionInfo fdInfo = (FieldDefinitionInfo)fields[i];
				
				into += fdInfo.SourceField;
				into += i < fields.Count - 1 ? COMMA : String.Empty;
	
				values += "?";
				values += i < fields.Count - 1 ? COMMA : String.Empty;

				OleDbParameter param = fdInfo.IsPrimaryKey ? GetCommandParameter(fdInfo, pkValue) : GetCommandParameter(fdInfo);

				command.Parameters.Add(param);
			}

			command.CommandText = String.Format(insertCommandText, connectionInfo.TableName, into, values);
			command.CommandType = CommandType.Text;

			return command;
		}

		/// <summary>
		/// Generates an OleDB command suitable to update data from a custom user store.
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="connectionInfo"></param>
		/// <param name="pkValue"></param>
		/// <returns></returns>
		private IDbCommand GetUpdateCommand(IDbConnection conn, DataConnectionInfo connectionInfo, Guid pkValue)
		{
			IDbCommand command = conn.CreateCommand();

			string set = String.Empty, where = String.Empty;
			const string updateCommandText = "UPDATE {0} SET {1} WHERE {2}";

			ArrayList fields = this.GetFields(connectionInfo.TableName);
			OleDbParameter pkParameter = null;

			for (int i = 0; i < fields.Count; i++)
			{
				FieldDefinitionInfo fdInfo = (FieldDefinitionInfo)fields[i];

			    if (fdInfo.IsPrimaryKey)
				{
					where = String.Format("{0} = ?", fdInfo.SourceField);
					pkParameter = GetCommandParameter(fdInfo, pkValue);
				}
				else
				{
					set += String.Format("{0} = ?{1}", fdInfo.SourceField, COMMA);
					OleDbParameter param = GetCommandParameter(fdInfo);
					command.Parameters.Add(param);
				}
			}

			if (pkParameter == null)
			{
				throw new ApplicationException("PK_NOT_FOUND");
			}

			command.Parameters.Add(pkParameter);

			#region Remove trailing space and comma
			set = set.Substring(0, set.Length - 2);
			#endregion

			command.CommandText = String.Format(updateCommandText, connectionInfo.TableName, set, where);
			command.CommandType = CommandType.Text;

			return command;
		}

		/// <summary>
		/// This version handles primary key fields
		/// </summary>
		/// <param name="fdInfo"></param>
		/// <param name="pkValue"></param>
		/// <returns></returns>
		private static OleDbParameter GetCommandParameter(FieldDefinitionInfo fdInfo, Guid pkValue)
		{
			OleDbParameter result = new OleDbParameter();
			
			result.ParameterName = fdInfo.SourceField;
			result.DbType = DbType.AnsiString;
			
			result.Value = pkValue.ToString();
			
			return result;
		}

		/// <summary>
		/// This version handles all field types other than primary key fields
		/// </summary>
		/// <param name="fdInfo"></param>
		/// <returns></returns>
		private OleDbParameter GetCommandParameter(FieldDefinitionInfo fdInfo)
		{
			OleDbParameter result = new OleDbParameter();

			Triplet triplet = this.GetFieldInfo(fdInfo);
	
			// Only for reference; OleDB does not support named parameters
			result.ParameterName = (string)triplet.First;

			result.DbType = (DbType)triplet.Second;
			result.Value  = triplet.Third;

			return result;
		}

		/// <summary>
		/// Gets a field name/type/value triplet.
		/// </summary>
		/// <param name="fdInfo"></param>
		/// <returns></returns>
		private Triplet GetFieldInfo(FieldDefinitionInfo fdInfo)
		{
			DbType fieldType = DbType.AnsiString;

			string fieldName  = fdInfo.SourceField;
			object fieldValue = null;

			IFormlet myFormlet = (IFormlet)formlets[fieldName];

			if (myFormlet == null)
			{
				throw new ApplicationException("IFORMLET_NOT_FOUND");
			}

			string xmlValue = myFormlet.GetValue(ContextUtil.GetTypeXml());

			TypeEvaluator evaluator = new TypeEvaluator(xmlValue, fdInfo);

			fieldType  = evaluator.GetFieldDbType();
			fieldValue = fdInfo.IsLinker ? this.ForeignKeyValue.ToString() : evaluator.GetFieldValue();

			Triplet result = new Triplet(fieldName, fieldType, fieldValue);

			return result;
		}
		#endregion
	}
}
